﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Activities;
using System.Data.SqlClient;
using System.ComponentModel;
using WFDataBaseProject.Helpers;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Data.Odbc;
using System.IO;
using System.Windows.Forms;
using System.Activities.Presentation.PropertyEditing;
using System.Activities.Presentation.Metadata;

namespace WFDataBaseProject
{
    [Designer(typeof(UCAsyncExecuteSqlActivityDesigner))]
    public class AsyncExecuteSqlActivity:AsyncCodeActivity
    {
        public AsyncExecuteSqlActivity()
        {
            AttributeTableBuilder builder = new AttributeTableBuilder();
            builder.AddCustomAttributes(typeof(AsyncExecuteSqlActivity), "FileName", new EditorAttribute(typeof(FilePickerEditor), typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(AsyncExecuteSqlActivity), "ConnectionString", new EditorAttribute(typeof(ConnectionStringEditor), typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(AsyncExecuteSqlActivity), "Query", new EditorAttribute(typeof(QueryEditor), typeof(DialogPropertyValueEditor)));
            MetadataStore.AddAttributeTable(builder.CreateTable());
        }

        #region arguments
        [RequiredArgument]
        [Description("The connection type sql server,oledb,odbc,oracle or other")]
        [Category("Inputs")]
        public InArgument<TypeOfConnection> ConnectionType { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The connection string to connect to the data base")]
        public InArgument<string> ConnectionString { get; set; }
        [Category("Inputs")]
        public InArgument<string> Query { get; set; }
        [Description("The file name form where to get the query")]
        [Category("Inputs")]
        public InArgument<string> FileName { get; set; }
        [RequiredArgument]
        [Description("The type of the source location whether it is inline,sql file or text file")]
        [Category("Inputs")]
        public InArgument<SqlSourceLocation> SourceLocation { get; set; }
        [RequiredArgument]
        [Description("The returned type whether it is a data table, a single record or just a none query execution")]
        [Category("Inputs")]
        public InArgument<TypeOfReturn> ReturnedType { get; set; }
        [Browsable(false)]
        public OutArgument<DataTable> ReturnedTable { get; set; }
        [Browsable(false)]
        public OutArgument<object> ReturnedScalar { get; set; }

        #endregion

        #region pivatefileds
        string filename;
        string query;
        TypeOfConnection connectiontype;
        string connectionstring;
        SqlSourceLocation sourcelocation;
        TypeOfReturn returnedtype;
        SqlCommand sqlcommmand;
        SqlDataReader reader;
        #endregion

        SqlCommand sqlcommand;
        protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
        {
            connectiontype = ConnectionType.Get<TypeOfConnection>(context);
            connectionstring = ConnectionString.Get(context);
            sourcelocation = SourceLocation.Get(context);
            returnedtype = ReturnedType.Get(context);

            query = string.Empty;
            
            switch (sourcelocation)
            {
                case SqlSourceLocation.InLine:
                    query = GetQueryFromSource(context, SqlSourceLocation.InLine);
                    break;
                case SqlSourceLocation.TextFile:
                    query = GetQueryFromSource(context, SqlSourceLocation.TextFile);
                    break;
                case SqlSourceLocation.SqlFile:
                    query = GetQueryFromSource(context, SqlSourceLocation.SqlFile);
                    break;
                default:
                    break;
            }

                        #region sql
                        SqlConnection sqlconnection = new SqlConnection(connectionstring);
                        using (sqlconnection)
                        {
                            try
                            {
                                sqlconnection.Open();
                                sqlcommmand = new SqlCommand(query, sqlconnection);
                                context.UserState = state;
                                switch (returnedtype)
                                {
                                    case TypeOfReturn.Request:
                                        return sqlcommmand.BeginExecuteReader(callback,state,CommandBehavior.Default);
                                    case TypeOfReturn.Scalar:
                                        return sqlcommmand.BeginExecuteReader(callback, state, CommandBehavior.SingleResult);        
                                    case TypeOfReturn.None:
                                        return sqlcommmand.BeginExecuteNonQuery(callback, state);
                                    default:
                                        throw new ApplicationException("A returned type chould be chosen");

                                }
                            }
                            catch (SqlException caught)
                            {
#if DEBUG
                                Debug.Print(caught.Message);
#endif
                                return null;
                            }

                        }

                        #endregion 
        }

        string GetQueryFromSource(CodeActivityContext context, SqlSourceLocation sourcelocation)
        {
            string query = string.Empty;

            switch (sourcelocation)
            {
                case SqlSourceLocation.InLine:
                    {
                        query = Query.Get<string>(context);
                        break;
                    }

                case SqlSourceLocation.TextFile:
                    {
                        GetFileName(context, FileFilter.Txt);
                        try
                        {
                            StreamReader reader = new StreamReader(filename);
                            Query.Set(context, reader.ReadToEnd());
                            query = Query.Get<string>(context);
                        }
                        catch (ApplicationException caught)
                        {
                            MessageBox.Show(caught.Message);
                        }

                        break;
                    }
                case SqlSourceLocation.SqlFile:
                    {
                        GetFileName(context, FileFilter.Sql);
                        try
                        {
                            StreamReader reader = new StreamReader(filename);
                            Query.Set(context, reader.ReadToEnd());
                            query = Query.Get<string>(context);
                        }
                        catch (ApplicationException caught)
                        {
                            MessageBox.Show(caught.Message);
                        }
                        break;
                    }
                default:
                    throw new ApplicationException("No source query found");
            }
            return query;
        }
        void GetFileName(ActivityContext context, FileFilter filefilter)
        {
            OpenFileDialog fileDialog;
            filename = FileName.Get<string>(context);
            switch (filefilter)
            {
                case FileFilter.Txt:
                    {
                        fileDialog = new OpenFileDialog();
                        fileDialog.Filter = "(*.txt)|*.txt";
                        if (fileDialog.ShowDialog() == DialogResult.OK)
                        {
                            filename = fileDialog.FileName;
                        }

                        break;
                    }
                case FileFilter.Sql:
                    {
                        fileDialog = new OpenFileDialog();
                        fileDialog.Filter = "(*.sql)|*.sql";
                        if (fileDialog.ShowDialog() == DialogResult.OK)
                        {
                            filename = fileDialog.FileName;
                        }
                        break;
                    }
                default:
                    {
                        throw new ApplicationException("The type of the input should be chosen");
                    }
            }

        }
 
        protected override void EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
        {
            switch (returnedtype)
            {
                case TypeOfReturn.Request:
                    reader = sqlcommmand.EndExecuteReader(result);
                    DataTable table = new DataTable(new Guid().ToString());
                    ReturnedTable.Set(context, table);
                    break;
                case TypeOfReturn.Scalar:
                    reader = sqlcommmand.EndExecuteReader(result);
                    ReturnedScalar.Set(context, reader[0]);
                    break;
                case TypeOfReturn.None:
                    sqlcommmand.EndExecuteNonQuery(result);
#if DEBUG
                    Debug.Print("Command executed successfully");
#endif
                    break;
                default:
                    throw new ApplicationException("A returned type should be chosen");
            }
        }
    }
}
